package com.fast.mybatis.util;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class MySqlDBUtil {
    private static Logger LOGGER = LoggerFactory.getLogger(MySqlDBUtil.class);

    private static String DRIVER = "com.mysql.jdbc.Driver";
    private static String URL = "";
    private static String USERNAME = "";
    private static String PASSWORD = "";

    private static final String SQL = "SELECT * FROM ";// 数据库操作

    private static Connection conn = null;


    public MySqlDBUtil(MysqlEntity mysqlEntity) {
        this.URL = "jdbc:mysql://" + mysqlEntity.getUrl() + ":" + mysqlEntity.getPort() + "/" + mysqlEntity.getDbName() + "?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=UTC";
        this.USERNAME = mysqlEntity.getUsername();
        this.PASSWORD = mysqlEntity.getPassword();
    }

    static {
        try {
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            LOGGER.error("can not load jdbc driver", e);
        }
    }

    /**
     * 获取数据库连接
     *
     * @return
     */
    public static Connection getConnection() {
        try {
            if (null == conn) {
                conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            }
        } catch (SQLException e) {
            LOGGER.error("get connection failure {}", e);
        }
        return conn;
    }

    /**
     * 关闭数据库连接
     *
     * @param conn
     */
    public static void closeConnection() {
        if (conn != null) {
            try {
                conn.close();
                conn = null;
            } catch (SQLException e) {
                LOGGER.error("close connection failure", e);
            }
        }
    }

    /**
     * 查询 sql
     *
     * @param sql 预编译的 SQL 语句
     * @return
     */
    public static ResultSet executeQuery(String sql) {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            // 获取PreparedStatement对象，通过连接对象获取
            pstmt = conn.prepareStatement(sql);
            // 执行查询并返回结果集
            rs = pstmt.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        // 返回结果集
        return rs;
    }

    public static List<String> getDbNames(Connection conn) {
        List<String> list = new ArrayList<String>();
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT `SCHEMA_NAME` ");
        sql.append("FROM `information_schema`.`SCHEMATA` ");
        sql.append("WHERE `SCHEMA_NAME` NOT IN ('information_schema','performance_schema','mysql','sys')");
        ResultSet rs = null;
        try {
            rs = executeQuery(sql.toString());
            while (rs.next()) {
                list.add(rs.getString("SCHEMA_NAME"));
            }
        } catch (SQLException e) {
            return null;
        }
        return list;
    }

    /**
     * 通过数据库获取的所有表名
     */
    public static List<TableEntity> getTableNames(String dbName) {
        List<TableEntity> tableNames = new ArrayList<>();
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT `TABLE_NAME`,`TABLE_COMMENT` ");
        sb.append("FROM `information_schema`.`TABLES` ");
        sb.append("WHERE `TABLE_SCHEMA`= '" + dbName + "'");
        try {
            ResultSet rs = executeQuery(sb.toString());
            while (rs.next()) {
                TableEntity tableEntity = new TableEntity();
                tableEntity.setTableName(rs.getString("TABLE_NAME"));
                tableEntity.setTableDes(rs.getString("TABLE_COMMENT"));
                tableNames.add(tableEntity);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return tableNames;
    }

    /**
     * 获取表中所有字段名称
     *
     * @param tableName 表名
     * @return
     */
    public List<TableFiledEntity> getColumnNames(String dbName, String tableName, String delimiter) {
        List<TableFiledEntity> list = new ArrayList<>();
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT `COLUMN_NAME`, `DATA_TYPE`, `COLUMN_COMMENT`, `COLUMN_KEY`, `COLUMN_DEFAULT` ");
        sb.append("FROM `information_schema`.`COLUMNS` ");
        sb.append("WHERE `TABLE_SCHEMA`= '" + dbName + "' ");
        sb.append("AND `TABLE_NAME` = '" + tableName + "' ORDER BY ordinal_position");
        try {
            ResultSet rs = executeQuery(sb.toString());
            TableFiledEntity fieldEntity = null;
            String type = "";
            while (rs.next()) {
                fieldEntity = new TableFiledEntity();
                fieldEntity.setDbFiledName(rs.getString("COLUMN_NAME"));
                // 获取字段名称,是否处理字母大写
                if (delimiter != null && delimiter != "") {
                    fieldEntity.setFiledName(StringUtil.splitField(fieldEntity.getDbFiledName(), delimiter));
                } else {
                    fieldEntity.setFiledName(fieldEntity.getDbFiledName());
                }
                type = rs.getString("DATA_TYPE");
                // 获取数据库字段类型
                fieldEntity.setDbFiledType(matchingDataBaseDataType(type));
                // 获取字段类型
                fieldEntity.setFiledType(matchingJavaBeanDataType(type));
                // 获取字段注释
                fieldEntity.setFiledMark(rs.getString("COLUMN_COMMENT"));
                // 获取是否为主键
                fieldEntity.setKey(rs.getString("COLUMN_KEY").equals("PRI") ? true : false);
                // 默认值
                fieldEntity.setDefaults(rs.getString("COLUMN_DEFAULT"));
                list.add(fieldEntity);
            }
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public String matchingDataBaseDataType(String type) {
        switch (type) {
            case "bigint":
                return "BIGINT";
            case "binary":
                return "BINARY";
            case "bit":
                return "BIT";
            case "blob":
                return "BLOB";
            case "tinyint":
                return "TINYINT";
            case "char":
                return "CHAR";
            case "date":
                return "DATE";
            case "datetime":
                // 该类型如果正常转换无法启动，使用VARCHAR
                return "VARCHAR";
            case "decimal":
                return "DECIMAL";
            case "double":
                return "DOUBLE";
            case "enum":
                // 该类型如果正常转换无法启动，使用VARCHAR
                return "ENUM";
            case "float":
                return "FLOAT";
            case "int":
                // 该类型如果正常转换无法启动，使用INTEGER
                return "INTEGER";
            case "integer":
                return "INTEGER";
            case "longblob":
                // 该类型如果正常转换无法启动，使用VARCHAR
                return "VARCHAR";
            case "longtext":
                // 该类型如果正常转换无法启动，使用VARCHAR
                return "LONGTEXT";
            case "mediumblob":
                // 该类型如果正常转换无法启动，使用VARCHAR
                return "VARCHAR";
            case "mediumint":
                // 该类型如果正常转换无法启动，使用INTEGER
                return "INTEGER";
            case "mediumtext":
                // 该类型如果正常转换无法启动，使用VARCHAR
                return "VARCHAR";
            case "set":
                // 该类型如果正常转换无法启动，使用VARCHAR
                return "VARCHAR";
            case "smallint":
                return "SMALLINT";
            case "text":
                // 该类型如果正常转换无法启动，使用VARCHAR
                return "VARCHAR";
            case "time":
                return "TIME";
            case "timestamp":
                return "TIMESTAMP";
            case "tinyblob":
                // 该类型如果正常转换无法启动，使用VARCHAR
                return "VARCHAR";
            case "tinytext":
                // 该类型如果正常转换无法启动，使用VARCHAR
                return "VARCHAR";
            case "varbinary":
                return "VARBINARY";
            case "varchar":
                return "VARCHAR";
            case "year":
                // 该类型如果正常转换无法启动，使用VARCHAR
                return "YEAR";
            default:
                return "VARCHAR";
        }
    }

    public String matchingJavaBeanDataType(String type) {

        switch (type) {
            // 整数
            case "tinyint":
                return "Integer";
            case "int":
                return "Integer";
            case "integer":
                return "Integer";
            case "mediumint":
                return "Integer";
            case "smallint":
                return "Integer";
            case "bigint":
                return "Long";
            // 小数
            case "float":
                return "Float";
            case "double":
                return "Double";
            case "decimal":
                return "BigDecimal";
            // Boolean
            case "bit":
                return "Boolean";
            // 字符串
            case "char":
                return "String";
            case "varchar":
                return "String";
            case "text":
                return "String";
            // 日期
            case "year":
                return "Date";
            case "date":
                return "Date";
            case "datetime":
                return "Date";
            case "time":
                return "Date";
            case "timestamp":
                return "Date";
            // 枚举类型
            case "enum":
                return "String";
            case "set":
                return "String";
            // 其他，以下属性不常用，如果需要自行加入对应的java类型
            case "longblob":
                return "";
            case "longtext":
                return "";
            case "mediumblob":
                return "";
            case "mediumtext":
                return "";
            case "tinyblob":
                return "";
            case "tinytext":
                return "";
            case "varbinary":
                return "";
            case "binary":
                return "";
            case "blob":
                return "Byte[]";
            default:
                return "VARCHAR";
        }
    }

    public static String JavaBeanDataType(String type) {
        switch (type) {
            case "Date":
                return "java.util.Date";
            case "BigDecimal":
                return "java.math.BigDecimal";
            default:
                return "";
        }
    }

}
